package tingshu.repository;

import lombok.SneakyThrows;
import tingshu.data_object.TidToCountDO;
import tingshu.util.DBUtil;
import tingshu.util.Log;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author houdaiqin
 * @date 2022/07/16 17:23
 **/
public class RelationRepo {
    //查询出每首音乐关联的专辑数
    @SneakyThrows
    public List<TidToCountDO> selectCountByTidListGroupByTidList(List<Integer> tidList){
        //%s是音频id的数组
        String sqlFormat="select tid,count(*) as ref_count from relations where tid in (%s) group by tid order by tid";
        StringBuilder sb=new StringBuilder();
        for(Integer tid:tidList){
            sb.append(tid).append(",");
        }
        sb.delete(sb.length() - 1, sb.length());
        String sql = String.format(sqlFormat, sb.toString());
        List<TidToCountDO> list=new ArrayList<>();
        try(Connection c= DBUtil.connection()){
            try(PreparedStatement ps=c.prepareStatement(sql)){
                Log.println("执行SQL"+ps.toString());
                try(ResultSet rs=ps.executeQuery()){
                    while (rs.next()){
                        TidToCountDO tidToCountDO=new TidToCountDO(
                                rs.getInt("tid"),
                                rs.getInt("ref_count")
                        );
                        list.add(tidToCountDO);
                    }
                }
            }
        }
        return list;
    }

    //通过aid查询出这个专辑包含的音频信息（tid）
    @SneakyThrows
    public List<Integer> selectTidListByAid(int aid){
        String sql="select tid from relations where aid=? order by rid";
        List<Integer> tidList=new ArrayList<>();
        try(Connection c=DBUtil.connection()){
            try(PreparedStatement ps=c.prepareStatement(sql)){
                ps.setInt(1,aid);

                Log.println("执行SQL"+ps.toString());
                try(ResultSet rs=ps.executeQuery()){
                    while (rs.next()){
                        tidList.add(rs.getInt("tid"));
                    }
                }
            }
        }
        return tidList;
    }
}